package com.atguigu.edusugar.mapper;


import com.atguigu.edusugar.bean.Cm;
import com.atguigu.edusugar.bean.Course;
import com.atguigu.edusugar.bean.Province;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;

/**
 SELECT sum(order_amount) AS order_amount
 FROM dws_trade_course_order_window
 WHERE toYYYYMMDD(stt) = 20221027
 SELECT
 course_name,
 sum(order_amount) AS order_amount
 FROM dws_trade_course_order_window
 WHERE toYYYYMMDD(stt) = 20221027
 GROUP BY course_name order by order_amount desc limit 5

 SELECT
 subject_name category_name, --subject_name
 sum(order_amount) AS order_amount
 FROM dws_trade_course_order_window
 WHERE toYYYYMMDD(stt) = 20221027
 GROUP BY subject_name

 SELECT
 province_name,
 count(1) AS order_count,
 sum(order_origin_total_amount) AS order_amount
 FROM dws_trade_course_order_window
 WHERE toYYYYMMDD(stt) = 20221027
 GROUP BY province_name
 */
public interface TradeMapper {
    @Select("SELECT sum(order_amount) AS order_amount\n" +
        "FROM dws_trade_course_order_window\n" +
        "WHERE toYYYYMMDD(stt) = #{date}")
    BigDecimal gmv(int date);  // 20221028
    
    @Select("SELECT\n" +
        "    course_name,\n" +
        "    sum(order_amount) AS order_amount\n" +
        "FROM dws_trade_course_order_window\n" +
        "WHERE toYYYYMMDD(stt) = #{date}\n" +
        "GROUP BY course_name order by order_amount desc limit 5")
    List<Course> gmvByCourse(int date);
    
    @Select("SELECT\n" +
        "    subject_name category_name,\n" +
        "    sum(order_amount) AS order_amount\n" +
        "FROM dws_trade_course_order_window\n" +
        "WHERE toYYYYMMDD(stt) = #{date}\n" +
        "GROUP BY subject_name")
    List<Cm> gmvByCm(int date);
    
    @Select("SELECT\n" +
        "    province_name,\n" +
        "    count(1) AS order_count,\n" +
        "    sum(order_origin_total_amount) AS order_amount\n" +
        "FROM dws_trade_course_order_window\n" +
        "WHERE toYYYYMMDD(stt) = #{date}\n" +
        "GROUP BY province_name")
    List<Province> province(int date);
}
